package com.asd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.asd.util.DBUtil;
import com.asd.util.PageBean;
import com.asd.vo.User;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class UserDao {
	//添加学生
	public int addUser(User user) throws SQLException {
		Connection con=DBUtil.getConnection();
		String sql="insert into users(class_id,user_name,user_pass,user_phone,user_sex) values(?,?,?,?,?)";
		QueryRunner runner = new QueryRunner();
		Object param[] = new Object[5];
		param[0]=user.getClass_id();
		param[1]=user.getUser_name();
		param[2]=user.getUser_pass();
		param[3]=user.getUser_phone();
		param[4]=user.getUser_sex();
		int ok = runner.update(con,sql,param);
		con.close();
		return ok;
	}
	
	//登录
	public User Logins(String name,String pass) throws Exception {
		Connection con=DBUtil.getConnection();
		String sql="select * from users where user_name=?";
		
		QueryRunner runner = new QueryRunner();

		User user = runner.query(con,sql,new BeanHandler<User>(User.class),name);
		con.close();
		return user;
	}
	
	//根据id查找
	public User findByID(int id) throws Exception {
		Connection con=DBUtil.getConnection();
		String sql="select * from users where user_id=?";
		
		QueryRunner runner = new QueryRunner();
		
		User user = runner.query(con,sql,new BeanHandler<User>(User.class),id);
		con.close();
		return user;
	}
	//修改学生
	public int update(User user) throws Exception {
		Connection con=DBUtil.getConnection();
		String sql="update users set user_name=?,user_sex=?,class_id=?,user_phone=?,user_wx=?where user_id=?";
		Object param[] = new Object[6];
		param[0]=user.getUser_name();
		param[1]=user.getUser_sex();
		param[2]=user.getClass_id();
		param[3]=user.getUser_phone();
		param[4]=user.getUser_wx();
		param[5]=user.getUser_id();

		QueryRunner runner = new QueryRunner();
		
		int n = runner.update(con,sql,param);
		con.close();
		return n;
	}
	
	//查出所有用户 分页查询
	public List show(int page) throws SQLException {
		Connection con=DBUtil.getConnection();
		int temp=(page-1)*10;
		String sql="select * from users where role_id=3 limit ?,10";
		QueryRunner runner = new QueryRunner();
		List list=runner.query(con, sql, temp,new BeanListHandler<User>(User.class));
		
		con.close();
		return list;
	}
	
	//查找学生人数
	public int count() throws SQLException {
		Connection con=DBUtil.getConnection();
		String sql="select count(*) from users where role_id=3";
		QueryRunner runner = new QueryRunner();
		Long n = runner.query(con,sql, new ScalarHandler<Long>());
		
		con.close();
		return n.intValue();
	}
	
	// 删除用户
		public int delete(int user_id) throws Exception {
			Connection con=DBUtil.getConnection();
			String sql="delete  from users where user_id=?";

			QueryRunner runner = new QueryRunner();
			int n = runner.update(con,sql,user_id);
			con.close();
			return n;
		}
	public static void main(String[] args) throws Exception {
		UserDao dao=new UserDao();
//		User user = dao.findByID(2);
//		user.setUser_name("654");
//		dao.updata(user);
		//增加测试数据
//		for (int i = 100; i < 209; i++) {
//		     dao.addUser(new User(i,i%5+1,3,"测试学生"+i,"123","456","男"));
//		}
		PageBean pb= new PageBean();
		pb.setTotal(110);
		pb.setCurPage(8);
		pb.show();
		System.out.println();

	}
}
